﻿using System;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Text;
using E4RPTools;
using System.Windows.Forms;
using ReferenciasSibs;
using ReferenciasSibs.DataSet;

namespace ReferenciasSIBS
{
    public class Controller
    {
        private SqlConnection _connection;

        public IWin32Window OwnerWindow { get; set; }

        public event EventHandler<GenerateReferencesProgress> GeneratedReference; 

        #region Private Methods

        private void OnGeneratedReference(int minimum, int maximum, int value)
        {
            EventHandler<GenerateReferencesProgress> eventHandler = GeneratedReference;
            if (eventHandler != null)
                eventHandler(this, new GenerateReferencesProgress(minimum, maximum, value));
        }

        private void PublishAndShowErrorMessage(Exception e, string errorMessage)
        {            
            ExceptionPublisher.Publish(ConfigurationManager.AppSettings["LogFile"], e);
            ExceptionPublisher.ShowErrorMessage(OwnerWindow, errorMessage, "Referências Sibs");
        }

        private void FixConnection()
        {
            if (_connection.State == ConnectionState.Open)
                _connection.Close();
            _connection.Open();
        }
        
        private bool ImportRejectionHeader(string line, RejeicaoDataSet dataSet)
        {
            var header = dataSet.Header.NewHeaderRow();
            header.TipoRegisto = line.Substring(0, 1);
            header.TipoFicheiro = line.Substring(1, 4);
            if (header.TipoFicheiro != "AEPR")
            {
                MessageBox.Show(OwnerWindow, "O tipo do registo é inválido.", "Ficheiro de Rejeição - AEPR", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return false;
            }
            header.IdInstituicaoDestino = Convert.ToInt64(line.Substring(5, 8));
            header.IdInstituicaoOrigem = Convert.ToInt64(line.Substring(13, 8));
            header.DataProcessamento = DateTime.MinValue;// line.Substring(21, 9);
            header.IdUltimoFicheiroEnviado = Convert.ToInt64(line.Substring(30, 9));
            header.Entidade = Convert.ToInt64(line.Substring(39, 5));
            header.TipoFicheiroOriginal = "";// Convert.ToInt64(line.Substring(44, 4));
            header.DataProcessamentoOriginal = DateTime.MinValue;//Convert.ToInt64(line.Substring(48, 9));
            dataSet.Header.AddHeaderRow(header);
            return true;
        }

        private static void ImportRejectionDetail(string line, RejeicaoDataSet dataSet)
        {
            var detail = dataSet.Detalhe.NewDetalheRow();
            detail.TipoRegisto = line.Substring(0, 1);
            detail.RegistoOriginal = line.Substring(1, 50);
            detail.CodigoErro = line.Substring(51, 3);
            detail.DescritivoErro = line.Substring(54, 33);
            detail.Filler = line.Substring(87, 13);
            dataSet.Detalhe.AddDetalheRow(detail);
        }

        private static void ImportRejectionTrailer(string line, RejeicaoDataSet dataSet)
        {
            var trailer = dataSet.Trailer.NewTrailerRow();
            trailer.TipoRegisto = line.Substring(0, 1);
            trailer.NumeroRegistosDetalhe = Convert.ToInt64(line.Substring(1, 8));            
            dataSet.Trailer.AddTrailerRow(trailer);
        }

        private static DateTime GetDate(string date)
        {
            var year = Convert.ToInt32(date.Substring(0, 4));
            var month = Convert.ToInt32(date.Substring(4, 2));
            var day = Convert.ToInt32(date.Substring(6, 2));
            return new DateTime(year, month, day);
        }

        private bool ImportErrorHeader(string line, ErroDataSet dataSet)
        {
            var header = dataSet.Header.NewHeaderRow();
            header.TipoRegisto = line.Substring(0, 1);
            header.TipoFicheiro = line.Substring(1, 4);
            if (header.TipoFicheiro != "AEPE")
            {
                MessageBox.Show(OwnerWindow, "O tipo do registo é inválido.", "Ficheiro de Erros - AEPE", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return false;
            }
            header.IdInstituicaoDestino = Convert.ToInt64(line.Substring(5, 8));
            header.IdInstituicaoOrigem = Convert.ToInt64(line.Substring(13, 8));
            if (line.Substring(21, 9) != "000000000") header.DataProcessamento = GetDate(line.Substring(21, 9));
            if (line.Substring(30, 9) != "000000000") header.IdUltimoFicheiroEnviado = GetDate(line.Substring(30, 9));
            header.Entidade = Convert.ToInt64(line.Substring(39, 5));
            header.TipoFicheiroOriginal = line.Substring(44, 4);
            if (line.Substring(48, 9) != "000000000") header.DataProcessamentoOriginal = GetDate(line.Substring(48, 9));
            header.Filler = line.Substring(57, 43);
            dataSet.Header.AddHeaderRow(header);
            return true;
        }

        private static void ImportErrorDetail(string line, ErroDataSet dataSet)
        {
            var detail = dataSet.Detalhe.NewDetalheRow();
            detail.TipoRegisto = line.Substring(0, 1);
            detail.RegistoOriginal = line.Substring(1, 50);
            detail.CodigoErro = line.Substring(51, 3);
            detail.DescritivoErro = line.Substring(54, 33);
            detail.Filler = line.Substring(87, 13);
            dataSet.Detalhe.AddDetalheRow(detail);
        }

        private static void ImportErrorTrailer(string line, ErroDataSet dataSet)
        {
            var trailer = dataSet.Trailer.NewTrailerRow();
            trailer.TipoRegisto = line.Substring(0, 1);
            trailer.NumeroRegistos = Convert.ToInt64(line.Substring(1, 8));
            dataSet.Trailer.AddTrailerRow(trailer);
        }

        private bool ImportMovementsFileHeader(string line, MovimentoDataSet dataSet)
        {
            var header = dataSet.Header.NewHeaderRow();
            header.TipoRegisto = line.Substring(0, 1);
            header.TipoFicheiro = line.Substring(1, 4);
            if (header.TipoFicheiro != "MEPS")
            {
                MessageBox.Show(OwnerWindow, "O tipo do registo é inválido.", "Ficheiro de Movimentos - MEPS", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return false;
            }
            header.IdInstituicaoOrigem= Convert.ToInt64(line.Substring(5, 8));
            header.IdInstituicaoDestino = Convert.ToInt64(line.Substring(13, 8));
            if (line.Substring(21, 9) != "000000000") header.DataProcessamento = GetDate(line.Substring(21, 9));
            if (line.Substring(30, 9) != "000000000") header.IdUltimoFicheiro = GetDate(line.Substring(30, 9));
            header.Entidade = Convert.ToInt64(line.Substring(39, 5));
            header.CodigoMoeda = Convert.ToInt64(line.Substring(44, 3));
            header.TaxaIVA = Convert.ToInt64(line.Substring(47, 2));
            header.IdentificacaoLog = line.Substring(49, 4);
            header.Filler = line.Substring(53, 47);
            dataSet.Header.AddHeaderRow(header);
            return true;
        }

        private static DateTime GetDateTime(string date)
        {
            var year = Convert.ToInt32(date.Substring(0, 4));
            var month = Convert.ToInt32(date.Substring(4, 2));
            var day = Convert.ToInt32(date.Substring(6, 2));
            var hours = Convert.ToInt32(date.Substring(8, 2));
            var minutes = Convert.ToInt32(date.Substring(10, 2));
            return new DateTime(year, month, day, hours, minutes, 0, 0);
        }

        private static decimal GetMoney(string value)
        {
            var va = Convert.ToInt64(value.Substring(0, value.Length - 2));
            decimal fraction = Convert.ToDecimal(value.Substring(value.Length - 2, 2)) / 100;
            return va + fraction;
        }

        private static void ImportMovementsFileDetail(string line, MovimentoDataSet dataSet)
        {
            var detail = dataSet.Detalhe.NewDetalheRow();
            detail.TipoRegisto = line.Substring(0, 1);
            detail.CodigoProcessamento = Convert.ToInt64(line.Substring(1, 2));
            detail.IdentificacaoLogSibs = Convert.ToInt64(line.Substring(3, 4));
            detail.NumeroLogSibs = Convert.ToInt64(line.Substring(7, 8));
            detail.DataHoraTransaccaoCliente = GetDateTime(line.Substring(15, 12));
            detail.MontantePago = GetMoney(line.Substring(27, 10));
            detail.Tarifa = GetMoney(line.Substring(37, 5));
            detail.TipoTerminal = line.Substring(42, 2);
            detail.IdentificacaoTerminal = line.Substring(44, 10);
            detail.IdentificacaoTransaccao = line.Substring(54, 5);
            detail.LocalidadeTerminal = line.Substring(59, 15);
            detail.ReferenciaPagamento = line.Substring(74, 9);
            detail.ModoEnvioComunicacao = line.Substring(83, 1);
            detail.CodigoRespostaEmpresa = line.Substring(84, 1);
            detail.NumeroIdentificacaoResposta = line.Substring(85, 12);
            detail.Filler = line.Substring(97, 3);
            dataSet.Detalhe.AddDetalheRow(detail);
        }

        private static void ImportMovementsFileTrailer(string line, MovimentoDataSet dataSet)
        {
            var trailer = dataSet.Trailer.NewTrailerRow();
            trailer.TipoRegisto = line.Substring(0, 1);
            trailer.NumeroRegistosDetalhe = Convert.ToInt64(line.Substring(1, 8));
            trailer.MontanteTotalTransaccoes = GetMoney(line.Substring(9, 17));
            trailer.TotalTarifacao = GetMoney(line.Substring(26, 12));
            trailer.IVA = Convert.ToDecimal(line.Substring(38, 12));
            trailer.Filler = line.Substring(50, 50);
            dataSet.Trailer.AddTrailerRow(trailer);
        }

        private bool ImportClientsFileHeader(string line, ClienteDataSet dataSet)
        {
            var header = dataSet.Header.NewHeaderRow();
            header.TipoRegisto = line.Substring(0, 1);
            header.TipoFicheiro = line.Substring(1, 4);
            if (header.TipoFicheiro != "AEPS")
            {
                MessageBox.Show(OwnerWindow, "O tipo do registo é inválido.", "Ficheiro de Clientes - AEPS", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return false;
            }
            header.IdInstituicaoOrigem = Convert.ToInt64(line.Substring(5, 8));
            header.IdInstituicaoDestino = Convert.ToInt64(line.Substring(13, 8));
            if (line.Substring(21, 9) != "000000000") header.DataProcessamento = GetDate(line.Substring(21, 9));
            if (line.Substring(30, 9) != "000000000") header.IdUltimoFicheiroEnviado = GetDate(line.Substring(30, 9));
            header.Entidade = Convert.ToInt64(line.Substring(39, 5));
            header.CodigoMoeda = Convert.ToInt64(line.Substring(44, 3));
            header.Filler = line.Substring(47, 3);
            dataSet.Header.AddHeaderRow(header);
            return true;
        }

        private static void ImportClientsFileDetail(string line, ClienteDataSet dataSet)
        {
            var detail = dataSet.Detalhe.NewDetalheRow();
            detail.TipoRegisto = line.Substring(0, 1);
            detail.CodigoProcessamento = Convert.ToInt64(line.Substring(1, 2));
            detail.Referencia = Convert.ToInt64(line.Substring(3, 9));
            if (line.Substring(12, 8) != "00000000") detail.DataLimitePagamento =  GetDate(line.Substring(12, 8));
            if (line.Substring(20, 10) != "9999999999") detail.MontanteMaximo = GetMoney(line.Substring(20, 10));
            if (line.Substring(30, 8) != "00000000") detail.DataInicioPagamento = GetDate(line.Substring(30, 8));
            detail.MontanteMinimo = GetMoney(line.Substring(38, 10));
            detail.Filler = line.Substring(48, 2);
            dataSet.Detalhe.AddDetalheRow(detail);
        }

        private static void ImportClientsFileTrailer(string line, ClienteDataSet dataSet)
        {
            var trailer = dataSet.Trailer.NewTrailerRow();
            trailer.TipoRegisto = line.Substring(0, 1);
            trailer.NumeroRegistos = Convert.ToInt64(line.Substring(1, 8));
            trailer.Filler = line.Substring(9, 41);
            dataSet.Trailer.AddTrailerRow(trailer);
        }

        public CondominioDataSet HouseRentals()
        {
            FixConnection();
            var sqlTransaction = _connection.BeginTransaction();
            try
            {
                CondominioDataSet dataSet = Database.GetHouseRentals(sqlTransaction);
                sqlTransaction.Commit();
                return dataSet;
            }
            catch (Exception e)
            {
                sqlTransaction.Rollback();
                PublishAndShowErrorMessage(e, "Não foi possível ler a lista de condomínios.");
                return new CondominioDataSet();
            }
        }

        private static string HouseRentalConnectionString(string houseRentalId)
        {
            var dataSource = ConfigurationManager.AppSettings["FonteDados"];
            var databaseName = String.Format("PRI{0}", houseRentalId);
            var userName = ConfigurationManager.AppSettings["User"];
            var password = ConfigurationManager.AppSettings["Password"];
            return string.Format("Data Source={0};Initial Catalog={1};User ID={2};Password={3};MultipleActiveResultSets=True;", dataSource, databaseName, userName, password);
        }

        private bool LoginHouseRental(IDbConnection connection, string houseRentalId)
        {
            try
            {
                connection.Open();
            }
            catch (Exception e)
            {
                PublishAndShowErrorMessage(e, string.Format("Não foi possível ligar à base de dados do condomínio '{0}'.", houseRentalId));
                return false;
            }
            return true;
        }

        private TipoDocumentoDataSet GetDocumentTypes(string houseRentalId)
        {
            var connection = new SqlConnection(HouseRentalConnectionString(houseRentalId));
            if (!LoginHouseRental(connection, houseRentalId)) return new TipoDocumentoDataSet();
            var sqlTransaction = connection.BeginTransaction();
            try
            {
                TipoDocumentoDataSet dataSet = Database.GetDocumentTypes(sqlTransaction);
                sqlTransaction.Commit();
                connection.Close();
                return dataSet;
            }
            catch (Exception e)
            {
                sqlTransaction.Rollback();
                connection.Close();
                PublishAndShowErrorMessage(e, "Não foi possível ler os tipos e documento.");
                return new TipoDocumentoDataSet();
            }
        }

        private SerieDataSet GetSerials(string houseRentalId)
        {
            var connection = new SqlConnection(HouseRentalConnectionString(houseRentalId));
            if (!LoginHouseRental(connection, houseRentalId)) return new SerieDataSet();
            var sqlTransaction = connection.BeginTransaction();
            try
            {
                SerieDataSet dataSet = Database.GetSerials(sqlTransaction);
                sqlTransaction.Commit();
                connection.Close();
                return dataSet;
            }
            catch (Exception e)
            {
                sqlTransaction.Rollback();
                connection.Close();
                PublishAndShowErrorMessage(e, "Não foi possível ler as séries.");
                return new SerieDataSet();
            }
        }

        private DocumentoDataSet GetDocuments(string houseRentalId, string houseRental, DateTime beginDate, DateTime endDate, string documentType, string serial)
        {
            var connection = new SqlConnection(HouseRentalConnectionString(houseRentalId));
            if (!LoginHouseRental(connection, houseRentalId)) return new DocumentoDataSet();
            var sqlTransaction = connection.BeginTransaction();
            try
            {
                DocumentoDataSet dataSet = Database.GetDocumentsToReference(sqlTransaction, houseRentalId, houseRental, serial, documentType, beginDate, endDate);
                sqlTransaction.Commit();
                connection.Close();
                return dataSet;
            }
            catch (Exception e)
            {
                sqlTransaction.Rollback();
                connection.Close();
                PublishAndShowErrorMessage(e, "Não foi possível ler os documentos a gerar referências.");
                return new DocumentoDataSet();
            }
        }

        private DocumentoDataSet GetDocuments(DateTime beginDate, DateTime endDate, string documentType, string serial)
        {
            var houseRentals = HouseRentals();
            var dataSet = new DocumentoDataSet();
            foreach (var houseRental in houseRentals.Condominio)
            {
                var documents = GetDocuments(houseRental.Id, houseRental.Nome, beginDate, endDate, documentType, serial);
                dataSet.Merge(documents);
            }
            return dataSet;
        }

        private static string ValueToString(decimal value)
        {
            var integralPart = Math.Truncate(value);
            var fractionPart = (value - integralPart) == 0 ? "00" : Math.Truncate((value-integralPart) * 100).ToString();
            return integralPart + fractionPart;
        }

        private static string CheckDigit(string value)
        {
            double total = 0;
            for (var i = value.Length; i > 0; i--)
            {
                var weight = Math.Pow(10, i - 1) % 97;
                var indexValue = Convert.ToInt64(value.Substring(value.Length - i, 1));
                total += weight * indexValue;
            }
            var result = 98 - (total % 97);
            return result.ToString();
        }

        private static string GenerateReference(long referenceId, long entityNumber, decimal value)
        {
            string reference = entityNumber.ToString().PadLeft(5, '0');
            reference += referenceId.ToString().PadLeft(7, '0');
            reference += ValueToString(value).PadLeft(8, '0');
            reference += "00";
            string result = referenceId.ToString().PadLeft(7, '0') + CheckDigit(reference);
            return result.PadRight(9, '0');
        }

        private void UpdateReference(DocumentoDataSet.DocumentoRow document, string reference, long entityNumber)
        {
            var connection = new SqlConnection(HouseRentalConnectionString(document.Empresa));
            if (!LoginHouseRental(connection, document.Empresa)) return;
            var sqlTransaction = connection.BeginTransaction();
            try
            {                
                Database.UpdateReference(sqlTransaction, document.Filial, document.Serie, document.Tipo, document.Numero, reference, entityNumber);
                sqlTransaction.Commit();
                connection.Close();                
            }
            catch (Exception e)
            {
                sqlTransaction.Rollback();
                connection.Close();
                PublishAndShowErrorMessage(e, "Não foi possível actulizar a referência do documento.");                
            }
        }

        private void GenerateReference(SqlTransaction sqlTransaction, DocumentoDataSet.DocumentoRow document)
        {
            var documentReferenceId = Database.GetDocumentReferenceId(sqlTransaction, document.Empresa, document.Filial, document.Serie, document.Tipo, document.Numero);
            var referenceId = documentReferenceId != -1 ? documentReferenceId : Database.GetNextReferenceId(sqlTransaction);
            var entityNumber = Convert.ToInt64(ConfigurationManager.AppSettings["NumeroEntidade"]);
            var reference = GenerateReference(referenceId, entityNumber, document.ValorComIva);
            if (documentReferenceId == -1)
                Database.InsertReference(sqlTransaction, referenceId, document.Empresa, document.Filial, document.Serie, document.Tipo, document.Numero, document.ValorComIva, reference,
                    document.NomeEmpresa, document.Cliente, document.NomeCliente);
            else
                Database.UpdateReference(sqlTransaction, documentReferenceId, reference, document.ValorComIva, document.NomeEmpresa, document.Cliente, document.NomeCliente);
            UpdateReference(document, reference, entityNumber);
        }

        private DocumentoDataSet GetDocuments(string houseRentalId, DateTime beginDate, DateTime endDate, bool sent, bool notSent)
        {
            var connection = new SqlConnection(HouseRentalConnectionString(houseRentalId));
            if (!LoginHouseRental(connection, houseRentalId)) return new DocumentoDataSet();
            var sqlTransaction = connection.BeginTransaction();
            try
            {
                DocumentoDataSet dataSet = Database.GetDocumentsToSend(sqlTransaction, houseRentalId, sent, notSent, beginDate, endDate);
                sqlTransaction.Commit();
                connection.Close();
                return dataSet;
            }
            catch (Exception e)
            {
                sqlTransaction.Rollback();
                connection.Close();
                PublishAndShowErrorMessage(e, "Não foi possível ler os documentos a gerar referências.");
                return new DocumentoDataSet();
            }
        }

        private DocumentoDataSet GetDocumentsToSend(DateTime beginDate, DateTime endDate, bool sent, bool notSent)
        {
            var houseRentals = HouseRentals();
            var dataSet = new DocumentoDataSet();
            foreach (var houseRental in houseRentals.Condominio)
            {
                var documents = GetDocuments(houseRental.Id, beginDate, endDate, sent, notSent);
                dataSet.Merge(documents);
            }
            return dataSet;
        }

        private static void ExportFileHeader(TextWriter fileWriter, string fileName, string lastFileSent)
        {
            const string tipoRegisto = "0";
            const string tipoFicheiro = "AEPS";
            var idInstituicaoOrigem = ConfigurationManager.AppSettings["IdentificacaoEmpresa"];
            const string idInstituicaoDestino = "50000000";
            var dataProcessamento = fileName;
            var ultimoFicheiroEnviado = lastFileSent;
            var entidade = ConfigurationManager.AppSettings["NumeroEntidade"];
            const string codigoMoeda = "978";
            var line = tipoRegisto + tipoFicheiro + idInstituicaoOrigem + idInstituicaoDestino + dataProcessamento + ultimoFicheiroEnviado + entidade + codigoMoeda;
            fileWriter.WriteLine(line.PadRight(50));
        }

        private static void ExportFileDetail(TextWriter fileWriter, DocumentoDataSet.DocumentoRow document)
        {
            const string tipoRegisto = "1";
            const string codigoProcessamento = "80";
            var referencia = document.Referencia;
            var dataLimitePagamento = document.Data.AddYears(1).Year + document.Data.Month.ToString().PadLeft(2, '0') + document.Data.Day.ToString().PadLeft(2, '0');
            const string montanteMaximo = "9999999999";
            const string dataInicioPagamento = "00000000";
            var montanteMinimo = ValueToString(document.ValorComIva).PadLeft(10, '0');
            var line = tipoRegisto + codigoProcessamento + referencia + dataLimitePagamento + montanteMaximo + dataInicioPagamento + montanteMinimo;
            fileWriter.WriteLine(line.PadRight(50));
        }

        private static void ExportFileFooter(TextWriter fileWriter, DocumentoDataSet documents)
        {
            const string tipoRegisto = "9";
            var numeroDetalhe = documents.Documento.Count.ToString().PadLeft(8, '0');
            var line = (tipoRegisto + numeroDetalhe).PadRight(50);
            fileWriter.WriteLine(line);
        }

        private static void ExportFile(string filePath, string fileName, string lastFileSent, DocumentoDataSet documents)
        {
            StreamWriter fileWriter = null;
            try
            {
                fileWriter = new StreamWriter(filePath, false, Encoding.Default);
                ExportFileHeader(fileWriter, fileName, lastFileSent);
                foreach (DocumentoDataSet.DocumentoRow documento in documents.Documento)
                    ExportFileDetail(fileWriter, documento);
                ExportFileFooter(fileWriter, documents);
            }
            finally
            {                
                if(fileWriter != null)
                    fileWriter.Close();
            }
        }

        private DateTime GetDocumentDate(string reference, string houseRentalId)
        {
            var connection = new SqlConnection(HouseRentalConnectionString(houseRentalId));
            if (!LoginHouseRental(connection, houseRentalId)) return DateTime.MinValue;
            var sqlHRTransaction = connection.BeginTransaction();
            try
            {
                var date = Database.GetDocumentDate(sqlHRTransaction, houseRentalId, reference);
                sqlHRTransaction.Commit();
                connection.Close();
                return date;
            }
            catch (Exception e)
            {
                sqlHRTransaction.Rollback();
                connection.Close();
                PublishAndShowErrorMessage(e, "Não foi possível ler a data do documento de cliente.");
                return DateTime.MinValue;
            }
        }

        private void GetDocumentClientData(string reference, string houseRentalId, string houseRental, out string client, out string clientName)
        {
            var connection = new SqlConnection(HouseRentalConnectionString(houseRentalId));
            client = "";
            clientName = "";
            if (!LoginHouseRental(connection, houseRentalId)) return;
            var sqlHRTransaction = connection.BeginTransaction();
            try
            {
                var dataSet = Database.GetDocumentData(sqlHRTransaction, houseRentalId, houseRental, reference);
                client = dataSet.Documento[0].Cliente;
                clientName = dataSet.Documento[0].NomeCliente;
                sqlHRTransaction.Commit();
                connection.Close();
            }
            catch (Exception e)
            {
                sqlHRTransaction.Rollback();
                connection.Close();
                PublishAndShowErrorMessage(e, "Não foi possível ler o documento de cliente.");
            }
        }

        private void AppendCompanyClientData(MovimentoDataSet dataSet)
        {
            if (dataSet.Detalhe.Count == 0)
                return;
            FixConnection();
            var sqlTransaction = _connection.BeginTransaction();
            try
            {
                foreach (var detail in dataSet.Detalhe)
                {
                    var tempDataSet = new DocumentoDataSet();
                    Database.GetDocumentByReference(sqlTransaction, detail.ReferenciaPagamento, tempDataSet);
                    if (tempDataSet.Documento.Count == 0)
                        continue;
                    detail.CodigoEmpresa = tempDataSet.Documento[0].Empresa;
                    detail.NumeroDocumento = tempDataSet.Documento[0].Numero;
                    detail.TipoDocumento = tempDataSet.Documento[0].Tipo;
                    detail.DataDocumento = GetDocumentDate(tempDataSet.Documento[0].Referencia, detail.CodigoEmpresa);
                    if (tempDataSet.Documento[0].IsNomeEmpresaNull() || tempDataSet.Documento[0].NomeEmpresa == "")
                    {
                        detail.NomeEmpresa = Database.GetHouseRentalName(sqlTransaction, detail.CodigoEmpresa);
                        Database.UpdateCompanyData(sqlTransaction, tempDataSet.Documento[0].Referencia, detail.NomeEmpresa);
                    }
                    else
                        detail.NomeEmpresa = tempDataSet.Documento[0].NomeEmpresa;
                    if (tempDataSet.Documento[0].IsClienteNull() || tempDataSet.Documento[0].Cliente == "" ||
                        tempDataSet.Documento[0].IsNomeClienteNull() || tempDataSet.Documento[0].NomeCliente == "")
                    {
                        string client; string clientName;
                        GetDocumentClientData(tempDataSet.Documento[0].Referencia, detail.CodigoEmpresa, detail.NomeEmpresa, out client, out clientName);
                        Database.UpdateClientData(sqlTransaction, tempDataSet.Documento[0].Referencia, client, clientName);
                    }
                    Database.GetDocumentByReference(sqlTransaction, tempDataSet.Documento[0].Referencia, tempDataSet);
                    detail.CodigoCliente = tempDataSet.Documento[0].Cliente;
                    detail.NomeCliente = tempDataSet.Documento[0].NomeCliente;
                }
                sqlTransaction.Commit();
            }
            catch (Exception e)
            {
                sqlTransaction.Rollback();
                PublishAndShowErrorMessage(e, "Não foi possível obter informação dos detalhes do ficheiro de movimentos.");
            }
        }

        private void AppendCompanyClientData(ClienteDataSet dataSet)
        {
            if (dataSet.Detalhe.Count == 0)
                return;
            FixConnection();
            var sqlTransaction = _connection.BeginTransaction();
            try
            {
                foreach (var detail in dataSet.Detalhe)
                {
                    var tempDataSet = new DocumentoDataSet();
                    var reference = detail.Referencia.ToString().PadLeft(9, '0');
                    Database.GetDocumentByReference(sqlTransaction, reference, tempDataSet);
                    if(tempDataSet.Documento.Count == 0)
                        return;
                    detail.CodigoEmpresa = tempDataSet.Documento[0].Empresa;
                    detail.NumeroDocumento = tempDataSet.Documento[0].Numero;
                    detail.TipoDocumento = tempDataSet.Documento[0].Tipo;
                    detail.DataDocumento = GetDocumentDate(tempDataSet.Documento[0].Referencia, detail.CodigoEmpresa);
                    if (tempDataSet.Documento[0].IsNomeEmpresaNull() || tempDataSet.Documento[0].NomeEmpresa == "")
                    {
                        detail.NomeEmpresa = Database.GetHouseRentalName(sqlTransaction, detail.CodigoEmpresa);
                        Database.UpdateCompanyData(sqlTransaction, tempDataSet.Documento[0].Referencia, detail.NomeEmpresa);
                    }
                    else
                        detail.NomeEmpresa = tempDataSet.Documento[0].NomeEmpresa;
                    if (tempDataSet.Documento[0].IsClienteNull() || tempDataSet.Documento[0].Cliente == "" ||
                        tempDataSet.Documento[0].IsNomeClienteNull() || tempDataSet.Documento[0].NomeCliente == "")
                    {
                        string client; string clientName;
                        GetDocumentClientData(tempDataSet.Documento[0].Referencia, detail.CodigoEmpresa, detail.NomeEmpresa, out client, out clientName);
                        Database.UpdateClientData(sqlTransaction, tempDataSet.Documento[0].Referencia, client, clientName);
                    }
                    Database.GetDocumentByReference(sqlTransaction, tempDataSet.Documento[0].Referencia, tempDataSet);
                    detail.CodigoCliente = tempDataSet.Documento[0].Cliente;
                    detail.NomeCliente = tempDataSet.Documento[0].NomeCliente;
                }
                sqlTransaction.Commit();
            }
            catch (Exception e)
            {
                sqlTransaction.Rollback();
                PublishAndShowErrorMessage(e, "Não foi possível obter informação dos detalhes do ficheiro de movimentos.");
            }
        }

        #endregion

        public bool IsUserValid(string userName, string password)
        {
            var dataSource = ConfigurationManager.AppSettings["FonteDados"];
            var databaseName = ConfigurationManager.AppSettings["NomeBaseDados"];
            var connectionString = String.Format("Data Source={0};Initial Catalog={1};User ID={2};Password={3};MultipleActiveResultSets=True;", dataSource, databaseName, userName, password);
            try
            {
                _connection = new SqlConnection(connectionString);
                _connection.Open();
            }
            catch (Exception e)
            {
                PublishAndShowErrorMessage(e, "Utilizador sem acesso à base de dados.");
                return false;
            }
            return true;
        }

        public RejeicaoDataSet ImportRejectionsFile(string fileName)
        {
            var dataSet = new RejeicaoDataSet();
            var reader = new StreamReader(fileName, Encoding.Default, true);
            var lineNumber = 0;
            while (!reader.EndOfStream )
            {
                String line = reader.ReadLine();
                lineNumber++;
                if(line.Length != 100)
                {
                    MessageBox.Show(OwnerWindow, "O tamanho do registo é inválido na linha nº" + lineNumber, "Ficheiro de Rejeição - AEPR", MessageBoxButtons.OK, MessageBoxIcon.Error);
                    return dataSet;
                }
                switch(line.Substring(0,1))
                {
                    case "0":
                        if(!ImportRejectionHeader(line, dataSet))
                            return dataSet;
                        break;
                    case "1":
                        ImportRejectionDetail(line, dataSet);
                        break;
                    case "9":
                        ImportRejectionTrailer(line, dataSet);
                        break;
                }
            }
            return dataSet;
        }

        public ErroDataSet ImportErrorsFile(string fileName)
        {
            var dataSet = new ErroDataSet();
            var reader = new StreamReader(fileName, Encoding.Default, true);
            var lineNumber = 0;
            while (!reader.EndOfStream )
            {
                String line = reader.ReadLine();
                lineNumber++;
                if(line.Length != 100)
                {
                    MessageBox.Show(OwnerWindow, "O tamanho do registo é inválido na linha nº" + lineNumber, "Ficheiro de Erros - AEPE", MessageBoxButtons.OK, MessageBoxIcon.Error);
                    return dataSet;
                }
                switch(line.Substring(0,1))
                {
                    case "0":
                        if (!ImportErrorHeader(line, dataSet))
                            return dataSet;
                        break;
                    case "1":
                        ImportErrorDetail(line, dataSet);
                        break;
                    case "9":
                        ImportErrorTrailer(line, dataSet);
                        break;
                }
            }
            return dataSet;
        }       

        public MovimentoDataSet ImportMovementsFile(string fileName)
        {
            var dataSet = new MovimentoDataSet();
            var reader = new StreamReader(fileName, Encoding.Default, true);
            var lineNumber = 0;
            while (!reader.EndOfStream)
            {
                var line = reader.ReadLine();
                lineNumber++;
                if (line.Length != 100)
                {
                    MessageBox.Show(OwnerWindow, "O tamanho do registo é inválido na linha nº" + lineNumber, "Ficheiro de Movimentos - MEPS", MessageBoxButtons.OK, MessageBoxIcon.Error);
                    return dataSet;
                }
                switch (line.Substring(0, 1))
                {
                    case "0":
                        if (!ImportMovementsFileHeader(line, dataSet))
                            return dataSet;
                        break;
                    case "2":
                        ImportMovementsFileDetail(line, dataSet);
                        break;
                    case "9":
                        ImportMovementsFileTrailer(line, dataSet);
                        break;
                }
            }
            AppendCompanyClientData(dataSet);
            return dataSet;
        }

        public ClienteDataSet ImportClientsFile(string fileName)
        {
            var dataSet = new ClienteDataSet();
            var reader = new StreamReader(fileName, Encoding.Default, true);
            var lineNumber = 0;
            while (!reader.EndOfStream)
            {
                String line = reader.ReadLine();
                lineNumber++;
                if (line.Length != 50)
                {
                    MessageBox.Show(OwnerWindow, "O tamanho do registo é inválido na linha nº" + lineNumber, "Ficheiro de Clientes - AEPS", MessageBoxButtons.OK, MessageBoxIcon.Error);
                    return dataSet;
                }
                switch (line.Substring(0, 1))
                {
                    case "0":
                        if (!ImportClientsFileHeader(line, dataSet))
                            return dataSet;
                        break;
                    case "1":
                        ImportClientsFileDetail(line, dataSet);
                        break;
                    case "9":
                        ImportClientsFileTrailer(line, dataSet);
                        break;
                }
            }
            AppendCompanyClientData(dataSet);
            return dataSet;
        }

        public TipoDocumentoDataSet GetDocumentTypes()
        {
            var houseRentals = HouseRentals();
            var dataSet = new TipoDocumentoDataSet();
            foreach (var houseRental in houseRentals.Condominio)
            {
                var documentTypes = GetDocumentTypes(houseRental.Id);
                dataSet.Merge(documentTypes);
            }
            return dataSet;
        }

        public SerieDataSet GetSerials()
        {
            var houseRentals = HouseRentals();
            var dataSet = new SerieDataSet();
            foreach (var houseRental in houseRentals.Condominio)
            {
                var serials = GetSerials(houseRental.Id);
                dataSet.Merge(serials);
            }
            return dataSet;
        }
        
        public void GenerateReferences(Control control, DateTime beginDate, DateTime endDate, string documentType, string serial)
        {
            DocumentoDataSet documents = GetDocuments(beginDate, endDate, documentType, serial);
            if(documents.Documento.Count == 0)
            {
                MessageBox.Show(OwnerWindow, "Dado o filtro efectuado não existem documentos a gerar referência.", "Gerar Referências");
                return;
            }
            var sqlTransaction = _connection.BeginTransaction();
            try
            {
                int position = 0;
                foreach (DocumentoDataSet.DocumentoRow document in documents.Documento)
                {                    
                    GenerateReference(sqlTransaction, document);
                    OnGeneratedReference(0, documents.Documento.Count, ++position);
                }
                sqlTransaction.Commit();                
            }
            catch (Exception e)
            {
                sqlTransaction.Rollback();
                PublishAndShowErrorMessage(e, "Não foi possível gerar referência para o documento.");
            }
        }

        public string GetLastFileSent()
        {
            var sqlTransaction = _connection.BeginTransaction();
            try
            {
                var lastFileSent = Database.GetLastFileSent(sqlTransaction);
                sqlTransaction.Commit();
                return lastFileSent;
            }
            catch (Exception e)
            {
                sqlTransaction.Rollback();
                PublishAndShowErrorMessage(e, "Não foi possível obter o último ficheiro enviado.");                
            }
            return "";
        }

        public void ExportFile(DateTime beginDate, DateTime endDate, bool sent, bool notSent, string filePath, string lastFileSent)
        {
            DocumentoDataSet documents = GetDocumentsToSend(beginDate, endDate, sent, notSent);
            if(documents.Documento.Count == 0)
                return;
            var sqlTransaction = _connection.BeginTransaction();
            try
            {
                var fileDate = DateTime.Now.Year + DateTime.Now.Month.ToString().PadLeft(2, '0') + DateTime.Now.Day.ToString().PadLeft(2, '0');
                var fileSequence = Database.GetNextFileSequence(sqlTransaction, fileDate);
                var fileName = filePath + "\\" + fileDate + fileSequence + ".dat";
                ExportFile(fileName, fileDate + fileSequence, lastFileSent, documents);
                Database.InsertFile(sqlTransaction, fileDate + fileSequence, fileDate, fileSequence);
                MessageBox.Show(OwnerWindow, "Foi gerado o ficheiro '" + fileName + "'.");
                foreach (DocumentoDataSet.DocumentoRow document in documents.Documento)
                    Database.SetAsSent(sqlTransaction, document.IdReferencia);
                sqlTransaction.Commit();
            }
            catch (Exception e)
            {
                sqlTransaction.Rollback();
                PublishAndShowErrorMessage(e, "Não foi possível gerar referência para o documento.");
            }
        }

        public void CloseConnections()
        {
            _connection.Close();
        }
    }
}
